package busterminal.persistence.jdbc;

import busterminal.entity.StoppingPoint;
import busterminal.entity.StoppingPointImplementation;
import busterminal.exception.BusinessException;
import busterminal.exception.DatabaseException;
import busterminal.persistence.StoppingPointDAO;
import busterminal.persistence.jdbc.proxy.StoppingPointProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Реализация интерфейса Data Access Object для остановочного пункта для работы с базой данных
 */
public class JdbcStoppingPointDAO extends AbstractJdbcDAO<StoppingPoint> implements StoppingPointDAO {

    /**
     * Добавить остановочный пункт в базу данных
     * @param entity ссылка на остановочный пункт
     */
    @Override
    public void create(StoppingPoint stoppingPoint) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("INSERT INTO StoppingPoint (Id, StoppingPointName) VALUES (?, ?)");
            stoppingPoint.setId(getNextId());
            preparedStatement.setInt(1, stoppingPoint.getId());
            preparedStatement.setString(2, stoppingPoint.getStoppingPointName());
            preparedStatement.execute();
        } catch (SQLException exception) {
            throw new DatabaseException("Cannot create new stopping point: " + stoppingPoint, exception);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException exception) {
                System.err.println("Cannot close statement");
                exception.printStackTrace(System.err);
            }
        }
    }

    /**
     * Прочитать остановочный пункт из базы данных
     * @param id идентификатор остановочного пункта
     * @return ссылка на прочитанный остановочный пункт
     */
    @Override
    public StoppingPoint read(Integer id) {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        StoppingPoint stoppingPoint = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id, StoppingPointName FROM StoppingPoint WHERE Id=" + id);
            if (resultSet.next()) {
                stoppingPoint = new StoppingPointImplementation();
                stoppingPoint.setId(resultSet.getInt("Id"));
                stoppingPoint.setStoppingPointName(resultSet.getString("StoppingPointName"));
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read stopping point with Id=" + id, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (stoppingPoint == null) {
            throw new BusinessException("Stopping point with Id=" + id + " not found");
        }
        return stoppingPoint;
    }

    /**
     * Обновить сведения об остановочном пункте в базе данные
     * @param entity ссылка на остановочный пункт
     */
    @Override
    public void update(StoppingPoint stoppingPoint) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("UPDATE StoppingPoint SET StoppingPointName=? WHERE Id=?");
            preparedStatement.setString(1, stoppingPoint.getStoppingPointName());
            preparedStatement.setInt(2, stoppingPoint.getId());
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot update stopping point with Id=" + stoppingPoint.getId(), e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Удалить остановочный пункт из базы данных
     * @param entity ссылка на остановочный пункт
     */
    @Override
    public void delete(StoppingPoint stoppingPoint) {
        delete(stoppingPoint.getId());
    }

    /**
     * Удалить остановочный пункт из базы данных
     * @param id идентификатор остановочного пункта
     */
    @Override
    public void delete(Integer id) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("DELETE FROM StoppingPoint WHERE Id=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot delete stopping point with Id=" + id, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Получить список всех остановочных пунктов из базы данных
     * @return список остановочных пунктов
     */
    @Override
    public List<StoppingPoint> findAll() {
        Connection connection = getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        List<StoppingPoint> stoppingPointList = new ArrayList<StoppingPoint>();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Id FROM StoppingPoint");
            while (resultSet.next()) {
                StoppingPoint stoppingPoint = new StoppingPointProxy();
                stoppingPoint.setId(resultSet.getInt("Id"));
                stoppingPointList.add(stoppingPoint);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read stopping point list", e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return stoppingPointList;
    }

    /**
     * Найти остановочный пункт по названию
     * @param name название остановочного пункта
     * @return ссылка на найденный остановочный пункт
     */
    @Override
    public StoppingPoint findByName(String name) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        StoppingPoint stoppingPoint = null;
        try {
            preparedStatement = connection.prepareStatement("SELECT Id, StoppingPointName FROM StoppingPoint WHERE StoppingPointName=?");
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                stoppingPoint = new StoppingPointImplementation();
                stoppingPoint.setId(resultSet.getInt("Id"));
                stoppingPoint.setStoppingPointName(resultSet.getString("StoppingPointName"));
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cannot read stopping point with name " + name, e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
        return stoppingPoint;
    }
}
